﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BCCommon;
using BCEntity.WeChatComponent.WeChatMediaRecord;
using Dapper;

namespace BCData.WeChatComponent.WeChatMediaRecord
{
    /// <summary>
    /// 微信媒体文件上传记录数据处理
    /// </summary>
    public class WeChatMediaRecordData : IWeChatMediaRecordData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT MediaId,Type,CreatedAt,FileName,CreateTime,Title,Description,MusicUrl,HQMusicUrl,Url,IsTemporary,LocalUrl, 
		                                        (SELECT 1 FROM WeChatMessagePushRecord B WHERE B.MediaId=A.MediaId) AS AlreadyPushMessage
                                        FROM WeChatMediaRecord A";

        public WeChatMediaRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public bool Add(WeChatMediaRecordEntity weChatMediaRecordEntity)
        {
            string sql = @"INSERT INTO WeChatMediaRecord(MediaId,Type,CreatedAt,FileName,Title,Description,MusicUrl,HQMusicUrl,Url,IsTemporary,LocalUrl)
                                            VALUES(?MediaId,?Type,?CreatedAt,?FileName,?Title,?Description,?MusicUrl,?HQMusicUrl,?Url,?IsTemporary,?LocalUrl);";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", weChatMediaRecordEntity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?Type", weChatMediaRecordEntity.Type.ToString(), DbType.AnsiString);
            parameters.Add("?CreatedAt", weChatMediaRecordEntity.CreatedAt, DbType.Int32, size: 11);
            parameters.Add("?FileName", weChatMediaRecordEntity.FileName, DbType.AnsiString, size: 100);
            parameters.Add("?Title", weChatMediaRecordEntity.Title, DbType.AnsiString, size: 50);
            parameters.Add("?Description", weChatMediaRecordEntity.Description, DbType.String);
            parameters.Add("?MusicUrl", weChatMediaRecordEntity.MusicUrl, DbType.AnsiString, size: 1000);
            parameters.Add("?HQMusicUrl", weChatMediaRecordEntity.HQMusicUrl, DbType.AnsiString, size: 1000);
            parameters.Add("?Url", weChatMediaRecordEntity.Url, DbType.AnsiString, size: 1000);
            parameters.Add("?IsTemporary", weChatMediaRecordEntity.IsTemporary, DbType.Boolean);
            parameters.Add("?LocalUrl", weChatMediaRecordEntity.LocalUrl, DbType.AnsiString, size: 1000);
            int result = this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
            return result > 0;
        }
        public WeChatMediaRecordEntity GetEntity(string mediaId)
        {
            string sql = string.Format("{0} WHERE MediaId=?MediaId;", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", mediaId, DbType.AnsiString, size: 100);
            return this.databaseContext.Slave.QueryEntity<WeChatMediaRecordEntity>(sql, parameters);
        }
        public void Delete(string mediaId)
        {
            string sql = @"DELETE FROM WeChatMediaRecord WHERE MediaId=?MediaId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", mediaId, DbType.AnsiString, size: 100);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public IEnumerable<WeChatMediaRecordEntity> GetRecords(string mediaId,string fileName, DateTime? start, DateTime? end, string fileType, bool? isTemporary, bool? ascending, out int dataCount, int pageIndex = 1, int pageSize = 10)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            if (!string.IsNullOrWhiteSpace(mediaId))
            {
                wheres.Add("MediaId=?MediaId");
                parameters.Add("?MediaId", mediaId, DbType.AnsiString, size: 100);
            }
            if (!string.IsNullOrWhiteSpace(fileName))
            {
                wheres.Add("FileName=?FileName");
                parameters.Add("?FileName", fileName, DbType.AnsiString, size: 100);
            }
            if (!string.IsNullOrWhiteSpace(fileType))
            {
                wheres.Add("Type=?Type");
                parameters.Add("?Type", fileType.ToString(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            if (isTemporary.HasValue)
            {
                wheres.Add("IsTemporary=?IsTemporary");
                parameters.Add("?IsTemporary", isTemporary.Value, DbType.Boolean);
            }
            //DELETE FROM WeChatMediaRecord WHERE IsTemporary=1 AND from_unixtime(CreatedAt+259200,'%Y-%m-%d %H:%i:%S')<now();
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM WeChatMediaRecord {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var result = this.databaseContext.Slave.QueryMultiple<WeChatMediaRecordEntity, int>(sql, parameters);

            dataCount = result.Item2;

            return result.Item1;
        }

        public bool CheckMediaExists(string mediaId)
        {
            string sql = @"SELECT 1 FROM WeChatMediaRecord WHERE MediaId=?MediaId LIMIT 1;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", mediaId, DbType.AnsiString, size: 100);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }

        public WeChatMediaRecordCountEntity Count()
        {
            return this.databaseContext.Slave.QueryEntity<WeChatMediaRecordCountEntity>("WeChatMediaRecordCount", CommandType.StoredProcedure);
        }

        public bool Update(WeChatMediaRecordEntity weChatMediaRecordEntity)
        {
            string sql = @"UPDATE WeChatMediaRecord SET 
                            Type=?Type,
                            CreatedAt=?CreatedAt,
                            FileName=?FileName,
                            Title=?Title,
                            Description=?Description,
                            MusicUrl=?MusicUrl,
                            HQMusicUrl=?HQMusicUrl,
                            Url=?Url,
                            IsTemporary=?IsTemporary,
                            LocalUrl=?LocalUrl
                        WHERE MediaId=?MediaId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", weChatMediaRecordEntity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?Type", weChatMediaRecordEntity.Type.ToString(), DbType.AnsiString);
            parameters.Add("?CreatedAt", weChatMediaRecordEntity.CreatedAt, DbType.Int32, size: 11);
            parameters.Add("?FileName", weChatMediaRecordEntity.FileName, DbType.AnsiString, size: 100);
            parameters.Add("?Title", weChatMediaRecordEntity.Title, DbType.AnsiString, size: 50);
            parameters.Add("?Description", weChatMediaRecordEntity.Description, DbType.String);
            parameters.Add("?MusicUrl", weChatMediaRecordEntity.MusicUrl, DbType.AnsiString, size: 1000);
            parameters.Add("?HQMusicUrl", weChatMediaRecordEntity.HQMusicUrl, DbType.AnsiString, size: 1000);
            parameters.Add("?Url", weChatMediaRecordEntity.Url, DbType.AnsiString, size: 1000);
            parameters.Add("?IsTemporary", weChatMediaRecordEntity.IsTemporary, DbType.Boolean);
            parameters.Add("?LocalUrl", weChatMediaRecordEntity.LocalUrl, DbType.AnsiString, size: 1000);
            int result = this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
            return result > 0;
        }

        public IEnumerable<WeChatMediaRecordEntity> GetTemporaryFileRecords()
        {
            string sql = @"{0} WHERE IsTemporary=1";
            return this.databaseContext.Slave.Query<WeChatMediaRecordEntity>(string.Format(sql,QUERY));
        }

        public bool UpdateMediaTypeForThumb(string mediaId, string localurl)
        {
            string sql = @"UPDATE WeChatMediaRecord SET 
                            Type='Thumb',
                            LocalUrl=?LocalUrl
                        WHERE MediaId=?MediaId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?MediaId", mediaId, DbType.AnsiString, size: 100);
            parameters.Add("?LocalUrl", localurl, DbType.AnsiString, size: 1000);
            int result = this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
            return result > 0;
        }
    }
}
